const { pool } = require('../config/db');
const { success, error } = require('../utils/response');
const { md5WithSalt } = require('../utils/md5Util');

// ===== 用户管理（管理员端）=====

// 获取所有用户（优化：批量获取借阅统计）
exports.getAllUsers = async (req, res, next) => {
  try {
    const page = Math.max(1, parseInt(req.query.page) || 1);
    const limit = Math.min(100, Math.max(1, parseInt(req.query.limit) || 10)); // 防止过大 limit
    const offset = (page - 1) * limit;

    const { user_type, keyword } = req.query;

    let whereClause = 'WHERE 1=1';
    const params = [];

    if (user_type) {
      whereClause += ' AND user_type = ?';
      params.push(user_type);
    }

    if (keyword) {
      whereClause += ' AND (username LIKE ? OR phone LIKE ?)';
      params.push(`%${keyword}%`, `%${keyword}%`);
    }

    // 查询用户列表
    const [users] = await pool.query(`
      SELECT
        id,
        phone,
        username,
        user_type,
        create_time
      FROM users
      ${whereClause}
      ORDER BY create_time DESC
      LIMIT ? OFFSET ?
    `, [...params, limit, offset]);

    // 查询总数
    const [total] = await pool.query(`SELECT COUNT(*) as count FROM users ${whereClause}`, params);
    const total_count = total[0].count;

    // 批量查询所有用户的借阅统计（避免 N+1）
    if (users.length > 0) {
      const userIds = users.map(u => u.id);
      const placeholders = userIds.map(() => '?').join(',');
      const [statsList] = await pool.query(`
        SELECT 
          user_id,
          COUNT(CASE WHEN status = 'borrowed' THEN 1 END) AS borrowed_count,
          COUNT(CASE WHEN status = 'returned' THEN 1 END) AS returned_count,
          COUNT(CASE WHEN status = 'overdue' THEN 1 END) AS overdue_count
        FROM borrow_records 
        WHERE user_id IN (${placeholders})
        GROUP BY user_id
      `, userIds);

      const statsMap = statsList.reduce((map, stat) => {
        map[stat.user_id] = stat;
        return map;
      }, {});

      users.forEach(user => {
        user.borrow_stats = statsMap[user.id] || {
          borrowed_count: 0,
          returned_count: 0,
          overdue_count: 0
        };
      });
    } else {
      users.forEach(user => {
        user.borrow_stats = { borrowed_count: 0, returned_count: 0, overdue_count: 0 };
      });
    }

    success(res, {
      users,
      pagination: {
        current: page,
        limit,
        total: total_count,
        pages: Math.ceil(total_count / limit)
      }
    });
  } catch (err) {
    next(err);
  }
};

// 获取用户详情
exports.getUserDetail = async (req, res, next) => {
  try {
    const userId = parseInt(req.params.id);
    if (isNaN(userId)) {
      return error(res, '无效的用户ID', 400);
    }

    const [users] = await pool.query(
      'SELECT id, phone, username, user_type, create_time FROM users WHERE id = ?',
      [userId]
    );

    if (users.length === 0) {
      return error(res, '用户不存在', 404);
    }

    const user = users[0];

    // 借阅统计
    const [borrowStats] = await pool.query(`
      SELECT
        COUNT(*) as total_borrows,
        COUNT(CASE WHEN status = 'borrowed' THEN 1 END) as current_borrows,
        COUNT(CASE WHEN status = 'returned' THEN 1 END) as returned_count,
        COUNT(CASE WHEN status = 'overdue' THEN 1 END) as overdue_count
      FROM borrow_records
      WHERE user_id = ?
    `, [userId]);

    // 当前借阅书籍
    const [currentBorrows] = await pool.query(`
      SELECT
        br.id,
        br.borrow_date,
        br.due_date,
        br.renew_count,
        b.id as book_id,
        b.name as book_name,
        b.author,
        b.cover
      FROM borrow_records br
      LEFT JOIN books b ON br.book_id = b.id
      WHERE br.user_id = ? AND br.status = 'borrowed'
      ORDER BY br.borrow_date DESC
    `, [userId]);

    // 预约数量
    const [reservations] = await pool.query(`
      SELECT COUNT(*) as count FROM reservations 
      WHERE user_id = ? AND status IN ('waiting', 'available')
    `, [userId]);

    user.borrow_stats = borrowStats[0];
    user.current_borrows = currentBorrows;
    user.reservation_count = reservations[0]?.count || 0;

    success(res, user);
  } catch (err) {
    next(err);
  }
};

// 创建用户（管理员）
exports.createUser = async (req, res, next) => {
  try {
    const { phone, password, username, user_type } = req.body;

    if (!phone || !password) {
      return error(res, '手机号和密码不能为空', 400);
    }

    // 校验手机号格式（可选增强）
    if (!/^\d{11}$/.test(phone.trim())) {
      return error(res, '请输入有效的11位手机号', 400);
    }

    const [existing] = await pool.query('SELECT id FROM users WHERE phone = ?', [phone]);
    if (existing.length > 0) {
      return error(res, '手机号已被注册', 400);
    }

    const hashedPassword = md5WithSalt(password); // 确保该函数不会抛错

    const [result] = await pool.query(
      'INSERT INTO users (phone, password, username, user_type) VALUES (?, ?, ?, ?)',
      [phone, hashedPassword, username?.trim() || '用户', user_type || 'student']
    );

    success(res, {
      id: result.insertId,
      phone,
      username: username || '用户',
      user_type: user_type || 'student'
    }, '用户创建成功');
  } catch (err) {
    next(err);
  }
};

// 更新用户信息（管理员）
exports.updateUser = async (req, res, next) => {
  try {
    const userId = parseInt(req.params.id);
    if (isNaN(userId)) {
      return error(res, '无效的用户ID', 400);
    }

    const { username, user_type } = req.body;
    if (!username && !user_type) {
      return error(res, '请提供要更新的信息', 400);
    }

    const [existing] = await pool.query('SELECT id FROM users WHERE id = ?', [userId]);
    if (existing.length === 0) {
      return error(res, '用户不存在', 404);
    }

    await pool.query(
      'UPDATE users SET username = COALESCE(?, username), user_type = COALESCE(?, user_type) WHERE id = ?',
      [username, user_type, userId]
    );

    success(res, { id: userId }, '用户信息更新成功');
  } catch (err) {
    next(err);
  }
};

// 重置用户密码（管理员）
exports.resetUserPassword = async (req, res, next) => {
  try {
    const userId = parseInt(req.params.id);
    if (isNaN(userId)) {
      return error(res, '无效的用户ID', 400);
    }

    const { newPassword } = req.body;
    if (!newPassword || newPassword.length < 6) {
      return error(res, '密码至少6位', 400);
    }

    const [existing] = await pool.query('SELECT id FROM users WHERE id = ?', [userId]);
    if (existing.length === 0) {
      return error(res, '用户不存在', 404);
    }

    const hashedPassword = md5WithSalt(newPassword);
    await pool.query('UPDATE users SET password = ? WHERE id = ?', [hashedPassword, userId]);

    success(res, { id: userId }, '密码重置成功');
  } catch (err) {
    next(err);
  }
};

// 删除用户（管理员）
exports.deleteUser = async (req, res, next) => {
  try {
    const userId = parseInt(req.params.id);
    if (isNaN(userId)) {
      return error(res, '无效的用户ID', 400);
    }

    const [existing] = await pool.query('SELECT id FROM users WHERE id = ?', [userId]);
    if (existing.length === 0) {
      return error(res, '用户不存在', 404);
    }

    // 检查是否有未归还图书
    const [borrows] = await pool.query(
      'SELECT COUNT(*) as count FROM borrow_records WHERE user_id = ? AND status = "borrowed"',
      [userId]
    );

    if (borrows[0].count > 0) {
      return error(res, '该用户有未归还图书，无法删除', 400);
    }

    // 推荐：先删子表记录 或 确保 DB 设置了 ON DELETE CASCADE
    await pool.query('DELETE FROM users WHERE id = ?', [userId]);

    success(res, { id: userId }, '用户删除成功');
  } catch (err) {
    next(err);
  }
};

// 获取用户借阅历史（管理员）
exports.getUserBorrowHistory = async (req, res, next) => {
  try {
    const userId = parseInt(req.params.id);
    if (isNaN(userId)) {
      return error(res, '无效的用户ID', 400);
    }

    const page = Math.max(1, parseInt(req.query.page) || 1);
    const limit = Math.min(50, Math.max(1, parseInt(req.query.limit) || 10));
    const offset = (page - 1) * limit;

    const [records] = await pool.query(`
      SELECT
        br.id,
        br.borrow_date,
        br.due_date,
        br.return_date,
        br.status,
        br.borrow_days,
        br.renew_count,
        b.id as book_id,
        b.name as book_name,
        b.author,
        b.cover
      FROM borrow_records br
      LEFT JOIN books b ON br.book_id = b.id
      WHERE br.user_id = ?
      ORDER BY br.borrow_date DESC
      LIMIT ? OFFSET ?
    `, [userId, limit, offset]);

    const [total] = await pool.query(
      'SELECT COUNT(*) as count FROM borrow_records WHERE user_id = ?',
      [userId]
    );

    success(res, {
      records,
      pagination: {
        current: page,
        limit,
        total: total[0].count,
        pages: Math.ceil(total[0].count / limit)
      }
    });
  } catch (err) {
    next(err);
  }
};

// 获取用户统计（管理员）
exports.getUserStats = async (req, res, next) => {
  try {
    const [stats] = await pool.query(`
      SELECT
        COUNT(*) as total_users,
        SUM(CASE WHEN user_type = 'student' THEN 1 ELSE 0 END) as student_count,
        SUM(CASE WHEN user_type = 'teacher' THEN 1 ELSE 0 END) as teacher_count,
        SUM(CASE WHEN user_type = 'staff' THEN 1 ELSE 0 END) as staff_count
      FROM users
    `);

    const [active] = await pool.query(`
      SELECT COUNT(DISTINCT user_id) as count
      FROM borrow_records
      WHERE borrow_date >= DATE_SUB(NOW(), INTERVAL 30 DAY)
    `);

    stats[0].active_users = active[0]?.count || 0;

    success(res, stats[0]);
  } catch (err) {
    next(err);
  }
};


// utils/md5Util.js
const crypto = require('crypto');
const SALT = process.env.PASSWORD_SALT || 'your_default_salt_here'; // 务必在 .env 中设置！

exports.md5WithSalt = (password) => {
  if (typeof password !== 'string') throw new Error('密码必须是字符串');
  return crypto.createHash('md5').update(password + SALT).digest('hex');
};